﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;

namespace WindowsFormsApplication1
{
    class DBManager
    {
        static string connectionString = "User Id=KPD; Password=KPD;Data Source=virtual/orcl";
        OracleConnection myConnection = new OracleConnection(connectionString);
        //Возвращает системное время сервера Oracle
        public string getDate()
        {
            OracleCommand myCommand = myConnection.CreateCommand();
            myConnection.Open();
            myCommand = new OracleCommand("select sysdate from dual", myConnection);
            OracleDataReader myreader = myCommand.ExecuteReader();
            myreader.Read();
            DateTime date = myreader.GetDateTime(0);
            myConnection.Close();
            return (date.ToString());
        }
        //Вставка времени прихода на работу
        public void beginTime(string id)
        { 
            myConnection.Open();
            OracleCommand myCommand = new OracleCommand("insert into WORK_DAY (EMP_ID, WD_BEGIN_TIME) values(" + id + ", SYSDATE)", myConnection);
            myCommand.ExecuteNonQuery();
            myConnection.Close();
        }
        //Вставка времени ухода с рабочего места
        public void endTime(string id)
        {
            myConnection.Open();
            OracleCommand myCommand = new OracleCommand("UPDATE WORK_DAY SET WD_END_TIME=SYSDATE WHERE EMP_ID=" + id + "", myConnection);
            myCommand.ExecuteNonQuery();
            myConnection.Close();   
        }

        public string getBeginTime(string id)
        {
            OracleCommand myCommand = myConnection.CreateCommand();
            myConnection.Open();
            myCommand = new OracleCommand("select WD_BEGIN_TIME FROM WORK_DAY WHERE EMP_ID="+id+"", myConnection);
            OracleDataReader myreader = myCommand.ExecuteReader();
            myreader.Read();
            DateTime date = myreader.GetDateTime(0);
            myConnection.Close();
            return (date.ToString());
        }

        public void copyToHistory(string id)
        {
            myConnection.Open();
            OracleCommand copyCommand = new OracleCommand("INSERT INTO WD_HISTORY (EMP_ID, WDH_BEGIN_TIME, WDH_END_TIME) SELECT EMP_ID, WD_BEGIN_TIME, WD_END_TIME FROM WORK_DAY WHERE EMP_ID=" + id + "", myConnection);
            OracleCommand deleteCommand = new OracleCommand("DELETE FROM  WORK_DAY WHERE EMP_ID=" + id + "", myConnection);
            copyCommand.ExecuteNonQuery();
            deleteCommand.ExecuteNonQuery();
            myConnection.Close();
        }
       
    }
}
